package com.baturu.ford.carvin.dal;

import com.baturu.offlineFord.dto.BtrDTO;
import com.baturu.offlineFord.dto.CarTypeDTO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Collection;
import java.util.List;

/**
 * @Author: chenjiahao
 * @Time: 2019/1/21
 */
public interface CarType1DAO {

    @Select("SELECT id FROM fordepc.`ford_parts_mapping` left join asd on asd = 123")
    List<String> test();

    @Select({"SELECT DISTINCT t.`engine` enginCode,t.transmission transmissionCode,t.driven drivenCode,t.ver verCode,t.carBody carBodyCode,\n" +
            "\tt1.en `engine`,\n" +
            "\tt2.en transmission,\n" +
            "\tt3.en driven,\n" +
            "\tsubstr(t.date, 1, 4) mj,\n" +
            "\tt4. NAME,\n" +
            "\tt.carType,\n" +
            "\tCASE upper(substr(t.vinCode, 1, 1))\n" +
            "WHEN 'L' THEN\n" +
            "\t'长安福特'\n" +
            "ELSE\n" +
            "\t'进口福特'\n" +
            "END subBrand,\n" +
            " CASE upper(t5.en)\n" +
            "WHEN 'SALOON' THEN\n" +
            "\t'轿车'\n" +
            "WHEN 'CABRIOLET' THEN\n" +
            "\t'敞篷跑车'\n" +
            "WHEN 'COUPE' THEN\n" +
            "\t'轿跑车'\n" +
            "WHEN 'VAN' THEN\n" +
            "\t'货车'\n" +
            "WHEN 'ESTATE' THEN\n" +
            "\t'旅行车'\n" +
            "END vehicleType,\n" +
            "if(t6.ch='',t6.en,t6.ch) version,concat(\"FORDEPC_V2_\",MD5(concat(t.driven,t.`engine`,t.ver,t.carBody,t.transmission,t.carType)),'_',substr(t.date, 1, 4)) btrid," +
            "concat(t.driven,'_',t.`engine`,'_',t.ver,'_',t.carBody,'_',t.transmission,'_',t.carType) optionCode",
            "FROM\n" +
            "\t`ford_epc_vin` t\n" +
            "LEFT JOIN ford_epc_text t1 ON t.`engine` = t1.idx\n" +
            "LEFT JOIN ford_epc_text t2 ON concat(t.transmission,'0') = t2.idx\n" +
            "LEFT JOIN ford_epc_text t3 ON concat(REPLACE(t.driven, ' ','0'),'0') = t3.idx\n" +
            "LEFT JOIN ford_epc_text t6 on concat(t.ver,\"0\") = t6.idx\n" +
            "INNER JOIN ford_epc_car_type t4 ON t4.carType = t.carType\n" +
            "AND t.date >= `begin`\n" +
            "AND t.date <= `end`\n" +
            "LEFT JOIN ford_epc_text t5 ON t5.idx = concat(t.carBody, '0') " +
            "where t.id> #{begin} and t.id <= #{end}"})
    List<CarTypeDTO> queryCarType(@Param("begin") Integer begin, @Param("end") Integer end);

    @Select("select btrid,optionCode from ford_epc_v2_btr")
    List<BtrDTO> queryBtrid();

    @Insert({"<script>insert into tmp_carType_20190505 (`ENGINE`,transmission,driven,mj,`name`,carType,subBrand,vehicleType,version,enginCode,transmissionCode,drivenCode,verCode,carBodyCode,btrid,optionCode) VALUES",
            "<foreach item = 'dto' index= 'index' collection = 'dtos' open = '(' separator='),(' close = ')'>",
            "#{dto.engine},#{dto.transmission},#{dto.driven},#{dto.mj},#{dto.name},#{dto.carType},#{dto.subBrand},#{dto.vehicleType},#{dto.version},#{dto.enginCode},#{dto.transmissionCode},#{dto.drivenCode},#{dto.verCode},#{dto.carBodyCode},#{dto.btrid},#{dto.optionCode}",
    "</foreach></script>"})
    void insertCarType(@Param("dtos") Collection<CarTypeDTO> dtos);


}
